package com.cloudweb.oa.controller;

import cn.js.fan.db.ListResult;
import cn.js.fan.db.ResultIterator;
import cn.js.fan.db.ResultRecord;
import cn.js.fan.db.SQLFilter;
import cn.js.fan.util.*;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cloudweb.oa.cache.UserCache;
import com.cloudweb.oa.entity.Account;
import com.cloudweb.oa.entity.Post;
import com.cloudweb.oa.entity.PostUser;
import com.cloudweb.oa.security.AuthUtil;
import com.cloudweb.oa.service.WorkflowStatService;
import com.cloudweb.oa.utils.ConfigUtil;
import com.cloudweb.oa.utils.I18nUtil;
import com.cloudweb.oa.vo.Result;
import com.cloudwebsoft.framework.db.JdbcTemplate;
import com.cloudwebsoft.framework.util.LogUtil;
import com.redmoon.oa.Config;
import com.redmoon.oa.dept.DeptDb;
import com.redmoon.oa.dept.DeptMgr;
import com.redmoon.oa.dept.DeptUserDb;
import com.redmoon.oa.flow.*;
import com.redmoon.oa.person.UserDb;
import com.redmoon.oa.pvg.Privilege;
import com.redmoon.oa.pvg.RoleDb;
import io.swagger.annotations.*;
import jxl.Workbook;
import jxl.write.*;
import net.bytebuddy.asm.Advice;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.sql.SQLException;
import java.util.*;

@Controller
public class WorkflowStatController {

    @Autowired
    AuthUtil authUtil;

    @Autowired
    HttpServletRequest request;

    @Autowired
    I18nUtil i18nUtil;

    @Autowired
    UserCache userCache;

    @Autowired
    WorkflowStatService workflowStatService;

    @Autowired
    ConfigUtil configUtil;

    @ApiOperation(value = "部门绩效列表", notes = "部门绩效列表", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "deptCode", value = "部门编码", required = true, dataType = "String"),
            @ApiImplicitParam(name = "op", value = "操作", required = false, dataType = "String"),
            @ApiImplicitParam(name = "page", value = "页码", required = false, dataType = "Integer"),
            @ApiImplicitParam(name = "pageSize", value = "每页条数", required = false, dataType = "Integer"),
    })
    @ApiResponses({ @ApiResponse(code = 200, message = "操作成功") })
    @PreAuthorize("hasAnyAuthority('admin', 'admin.flow')")
    @ResponseBody
    @RequestMapping(value = "/flow/stat/performance/listByDept", method = RequestMethod.POST, produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    public Result<Object> listByDept(@RequestParam(value="deptCode") String deptCode, @RequestParam(value="op", required = false) String op, @RequestParam(value="page", required = false, defaultValue = "1") Integer page, @RequestParam(value="pageSize", required = false, defaultValue = "20") Integer pageSize) {
        return new Result<>(workflowStatService.listByDept(deptCode, op, "", page, pageSize));
    }

    @ApiOperation(value = "按部门导出", notes = "按部门导出", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "deptCode", value = "部门编码", required = true, dataType = "String"),
            @ApiImplicitParam(name = "op", value = "操作", required = false, dataType = "String"),
            @ApiImplicitParam(name = "page", value = "页码", required = false, dataType = "Integer"),
            @ApiImplicitParam(name = "pageSize", value = "每页条数", required = false, dataType = "Integer"),
    })
    @RequestMapping("/flow/stat/exportListByDept")
    public void exportListByDept(HttpServletResponse response, @RequestParam(value="deptCode") String deptCode, @RequestParam(value="op", required = false) String op, @RequestParam(value="page", required = false, defaultValue = "1") Integer page, @RequestParam(value="pageSize", required = false, defaultValue = "20") Integer pageSize) throws IOException {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        String fileName = "绩效统计.xls";
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);

        OutputStream os = response.getOutputStream();
        try {
            Workbook wb = Workbook.getWorkbook(configUtil.getFile("templ/performance_stat.xls"));
            WritableWorkbook workbook = Workbook.createWorkbook(os, wb);
            WritableSheet sheet = workbook.getSheet(0);
            JSONObject jsonObject = workflowStatService.listByDept(deptCode, op, "export", page, pageSize);
            JSONArray list = jsonObject.getJSONArray("list");

            int i = 0;
            for (Object o : list) {
                JSONObject json = (JSONObject)o;
                Label label = new Label(1, i+1, json.getString("realName"));
                sheet.addCell(label);
                label = new Label(2, i+1, json.getString("deptName"));
                sheet.addCell(label);

                for (int k = 0; k<=12; k++) {
                    label = new Label(3+k, i+1, json.getString(String.valueOf(k)));
                    sheet.addCell(label);
                }

                label = new Label(15, i+1, json.getString("amount"));
                sheet.addCell(label);

                i++;
            }
            workbook.write();
            workbook.close();
            wb.close();
        } catch (Exception e) {
            LogUtil.getLog(getClass()).error(e);
        } finally {
            os.close();
        }
    }

    @ApiOperation(value = "部门绩效月统计", notes = "部门绩效月统计", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "deptCode", value = "部门编码", required = true, dataType = "String"),
            @ApiImplicitParam(name = "showYear", value = "年份", required = false, dataType = "Integer"),
            @ApiImplicitParam(name = "showMonth", value = "月份", required = false, dataType = "Integer"),
    })
    @ApiResponses({ @ApiResponse(code = 200, message = "操作成功") })
    @PreAuthorize("hasAnyAuthority('admin', 'admin.flow')")
    @ResponseBody
    @RequestMapping(value = "/flow/stat/performance/statDeptByMonth", method = RequestMethod.POST, produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    public Result<Object> statDeptByMonth(@RequestParam(value="deptCode") String deptCode, @RequestParam(value="showYear", required = false) Integer showYear, @RequestParam(value="showMonth", required = false) Integer showMonth) {
        if (StrUtil.isEmpty(deptCode)) {
            Privilege privilege = new Privilege();
            if (!privilege.isUserPrivValid(request, "admin")) {
                return new Result<>(false, i18nUtil.get("pvg_invalid"));
            }
        }
        String leafName = "全部";
        JSONObject json = new JSONObject();
        if (!StrUtil.isEmpty(deptCode)) {
            DeptMgr dir = new DeptMgr();
            DeptDb leaf = dir.getDeptDb(deptCode);
            leafName = leaf.getName();
        }

        Calendar cal = Calendar.getInstance();

        if (showYear == null) {
            showYear = cal.get(Calendar.YEAR);
        }
        if (showMonth == null) {
            showMonth = cal.get(Calendar.MONTH) + 1;
        }
        int totalWorkflow = 0;

        int days = DateUtil.getDayCount(showYear, showMonth - 1);

        double max = 200; // Y轴最大值

        Calendar showCal = Calendar.getInstance();
        String sql;
        JdbcTemplate jt = new JdbcTemplate();
        jt.setAutoClose(false);
        List<Double> al = new ArrayList<>();
        try {
            for (int i = 1; i <= days; i++) {
                showCal.set(showYear, showMonth - 1, i);

                String bdate = SQLFilter.getDateStr(DateUtil.format(showCal, "yyyy-MM-dd 00:00:00"), "yyyy-MM-dd HH:mm:ss");
                Calendar nextCal = Calendar.getInstance();
                nextCal.set(showYear, showMonth - 1, i + 1);
                String edate = SQLFilter.getDateStr(DateUtil.format(nextCal, "yyyy-MM-dd 00:00:00"), "yyyy-MM-dd HH:mm:ss");

                DeptDb deptDb = new DeptDb();
                deptDb = deptDb.getDeptDb(deptCode);
                Vector dv = new Vector();
                deptDb.getAllChild(dv, deptDb);
                String depts = StrUtil.sqlstr(deptCode);
                for (Object value : dv) {
                    deptDb = (DeptDb) value;
                    depts += "," + StrUtil.sqlstr(deptDb.getCode());
                }

                DeptUserDb dud = new DeptUserDb();
                String sqlUser = "select du.ID from dept_user du, users u where du.user_name=u.name and u.isValid=1 and du.DEPT_CODE in (" + depts + ")";
                int curpage = ParamUtil.getInt(request, "CPages", 1);
                int pagesize = ParamUtil.getInt(request, "pageSize", 20);

                ListResult lr = dud.listResult(sqlUser, curpage, pagesize);
                double sumPerf = 0.0;
                Vector v = lr.getResult();
                for (Object o : v) {
                    DeptUserDb pu = (DeptUserDb) o;
                    String user_name = pu.getUserName();
                    sql = "select sum(performance) from flow_my_action where (user_name=" + StrUtil.sqlstr(user_name) + " or proxy=" + StrUtil.sqlstr(user_name) + ") and is_checked=1 and checker=" + StrUtil.sqlstr(user_name) + " and receive_date>=" + bdate + " and receive_date<=" + edate + "";
                    ResultIterator ri = jt.executeQuery(sql);
                    double perf = 0.0;
                    if (ri.hasNext()) {
                        ResultRecord rr = ri.next();
                        perf = rr.getDouble(1);
                    }
                    sumPerf += perf;
                }

                totalWorkflow += sumPerf;
                al.add(sumPerf);
                if (max < sumPerf) {
                    max = sumPerf;
                }
            }
        } catch (SQLException e) {
            LogUtil.getLog(getClass()).error(e);
        } finally {
            jt.close();
        }

        JSONArray list = new JSONArray();
        for (int i = 1; i <= al.size(); i++) {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("name", String.valueOf(i));
            jsonObject.put("value", al.get(i - 1));
            list.add(jsonObject);
        }
        json.put("list", list);
        json.put("leafName", leafName);
        json.put("total", totalWorkflow);

        return new Result<>(json);
    }

    @ApiOperation(value = "部门绩效年统计", notes = "部门绩效年统计", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "deptCode", value = "部门编码", required = true, dataType = "String"),
            @ApiImplicitParam(name = "showYear", value = "年份", required = false, dataType = "Integer"),
    })
    @ApiResponses({ @ApiResponse(code = 200, message = "操作成功") })
    @PreAuthorize("hasAnyAuthority('admin', 'admin.flow')")
    @ResponseBody
    @RequestMapping(value = "/flow/stat/performance/statDeptByYear", method = RequestMethod.POST, produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    public Result<Object> statDeptByYear(@RequestParam(value="deptCode") String deptCode, @RequestParam(value="showYear", required = false) Integer showYear) {
        if (StrUtil.isEmpty(deptCode)) {
            Privilege privilege = new Privilege();
            if (!privilege.isUserPrivValid(request, "admin")) {
                return new Result<>(false, i18nUtil.get("pvg_invalid"));
            }
        }

        String leafName = "全部";
        if (!StrUtil.isEmpty(deptCode)) {
            DeptDb leaf = null;
            DeptMgr dir = new DeptMgr();
            leaf = dir.getDeptDb(deptCode);
            leafName = leaf.getName();
        }
        JSONObject json = new JSONObject();
        Calendar cal = Calendar.getInstance();

        if (showYear == null) {
            showYear = cal.get(Calendar.YEAR);
        }

        int totalWorkflow = 0;
        double max = 200; // Y轴最大值

        Calendar showCal = Calendar.getInstance();
        String sql;
        JdbcTemplate jt = new JdbcTemplate();
        jt.setAutoClose(false);
        List<Double> al = new ArrayList<>();
        try {
            for (int i = 0; i <= 11; i++) {
                showCal.set(showYear, i, 1);

                String bdate = SQLFilter.getDateStr(DateUtil.format(showCal, "yyyy-MM-dd 00:00:00"), "yyyy-MM-dd HH:mm:ss");

                Calendar nextCal = Calendar.getInstance();
                if (i <= 10) {
                    nextCal.set(showYear, i + 1, 1);
                } else {
                    nextCal.set(showYear + 1, 0, 1);
                }
                String edate = SQLFilter.getDateStr(DateUtil.format(nextCal, "yyyy-MM-dd 00:00:00"), "yyyy-MM-dd HH:mm:ss");

                DeptMgr dm = new DeptMgr();
                DeptUserDb du = new DeptUserDb();

                DeptDb deptDb = new DeptDb();
                deptDb = deptDb.getDeptDb(deptCode);
                Vector dv = new Vector();
                deptDb.getAllChild(dv, deptDb);
                String depts = StrUtil.sqlstr(deptCode);
                for (Object value : dv) {
                    deptDb = (DeptDb) value;
                    depts += "," + StrUtil.sqlstr(deptDb.getCode());
                }

                DeptUserDb jd = new DeptUserDb();
                UserDb ud = new UserDb();

                String sqlUser = "select du.ID from dept_user du, users u where du.user_name=u.name and u.isValid=1 and du.DEPT_CODE in (" + depts + ")";
                int curpage = ParamUtil.getInt(request, "CPages", 1);
                int pagesize = ParamUtil.getInt(request, "pageSize", 20);

                ListResult lr = jd.listResult(sqlUser, curpage, pagesize);
                String user_name = "", realName = "";

                double sum_perf = 0.0;
                Vector v = lr.getResult();
                for (Object o : v) {
                    DeptUserDb pu = (DeptUserDb) o;
                    user_name = pu.getUserName();

                    sql = "select sum(performance) from flow_my_action where (user_name=" + StrUtil.sqlstr(user_name) + " or proxy=" + StrUtil.sqlstr(user_name) + ") and is_checked=1 and checker=" + StrUtil.sqlstr(user_name) + " and receive_date>=" + bdate + " and receive_date<=" + edate + "";

                    ResultIterator ri = jt.executeQuery(sql);
                    double perf = 0.0;
                    if (ri.hasNext()) {
                        ResultRecord rr = (ResultRecord) ri.next();
                        perf = rr.getDouble(1);
                    }
                    sum_perf += perf;
                }

                totalWorkflow += sum_perf;
                al.add(sum_perf);
                if (max < sum_perf) {
                    max = sum_perf;
                }
            }
        } catch (SQLException e) {
            LogUtil.getLog(getClass()).error(e);
        } finally {
            jt.close();
        }

        JSONArray list = new JSONArray();
        for (int i = 1; i <= al.size(); i++) {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("name", String.valueOf(i));
            jsonObject.put("value", al.get(i - 1));
            list.add(jsonObject);
        }
        json.put("list", list);
        json.put("total", totalWorkflow);
        json.put("leafName", leafName);
        return new Result<>(json);
    }

    @ApiOperation(value = "部门人员绩效月统计", notes = "部门人员绩效月统计", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "deptCode", value = "部门编码", required = true, dataType = "String"),
            @ApiImplicitParam(name = "showYear", value = "年份", required = false, dataType = "Integer"),
            @ApiImplicitParam(name = "showMonth", value = "月份", required = false, dataType = "Integer"),
    })
    @ApiResponses({ @ApiResponse(code = 200, message = "操作成功") })
    @PreAuthorize("hasAnyAuthority('admin', 'admin.flow')")
    @ResponseBody
    @RequestMapping(value = "/flow/stat/performance/statDeptPersonByMonth", method = RequestMethod.POST, produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    public Result<Object> statDeptPersonByMonth(@RequestParam(value="deptCode") String deptCode, @RequestParam(value="showYear", required = false) Integer showYear, @RequestParam(value="showMonth", required = false) Integer showMonth) {
        if (StrUtil.isEmpty(deptCode)) {
            Privilege privilege = new Privilege();
            if (!privilege.isUserPrivValid(request, "admin")) {
                return new Result<>(false, i18nUtil.get("pvg_invalid"));
            }
        }

        String leafName = "全部";
        JSONObject json = new JSONObject();
        if (!StrUtil.isEmpty(deptCode)) {
            DeptDb leaf = null;
            DeptMgr dir = new DeptMgr();
            leaf = dir.getDeptDb(deptCode);
            leafName = leaf.getName();
        }

        Calendar cal = Calendar.getInstance();

        if (showYear == null) {
            showYear = cal.get(Calendar.YEAR);
        }
        if (showMonth == null) {
            showMonth = cal.get(Calendar.MONTH) + 1;
        }

        int totalWorkflow = 0;

        Calendar showCal = Calendar.getInstance();
        String sql;
        int i = 1;
        int days = DateUtil.getDayCount(showYear, showMonth - 1);

        showCal.set(showYear, showMonth - 1, i);

        String bdate = SQLFilter.getDateStr(DateUtil.format(showCal, "yyyy-MM-dd 00:00:00"), "yyyy-MM-dd HH:mm:ss");
        Calendar nextCal = Calendar.getInstance();
        nextCal.set(showYear, showMonth - 1, days + 1);
        String edate = SQLFilter.getDateStr(DateUtil.format(nextCal, "yyyy-MM-dd 00:00:00"), "yyyy-MM-dd HH:mm:ss");

        DeptDb deptDb = new DeptDb();
        deptDb = deptDb.getDeptDb(deptCode);
        Vector dv = new Vector();
        deptDb.getAllChild(dv, deptDb);
        String depts = StrUtil.sqlstr(deptCode);
        for (Object value : dv) {
            deptDb = (DeptDb) value;
            depts += "," + StrUtil.sqlstr(deptDb.getCode());
        }

        DeptUserDb jd = new DeptUserDb();
        UserDb ud = new UserDb();

        String sql_user = "select du.ID from dept_user du, users u where du.user_name=u.name and u.isValid=1 and du.DEPT_CODE in (" + depts + ")";
        Vector v = jd.listBySQL(sql_user);
        String user_name = "", realName = "";

        JSONArray list = new JSONArray();
        JdbcTemplate jt = new JdbcTemplate();
        jt.setAutoClose(false);
        try {
            for (Object o : v) {
                DeptUserDb pu = (DeptUserDb) o;
                ud = ud.getUserDb(pu.getUserName());
                user_name = ud.getName();
                realName = ud.getRealName();

                sql = "select sum(performance) from flow_my_action where (user_name=" + StrUtil.sqlstr(user_name) + " or proxy=" + StrUtil.sqlstr(user_name) + ") and is_checked=1 and checker=" + StrUtil.sqlstr(user_name) + " and receive_date>=" + bdate + " and receive_date<=" + edate + "";
                ResultIterator ri = jt.executeQuery(sql);
                double perf = 0.0;
                if (ri.hasNext()) {
                    ResultRecord rr = ri.next();
                    perf = rr.getDouble(1);

                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("name", realName);
                    jsonObject.put("value", perf);
                    list.add(jsonObject);
                }
                totalWorkflow += perf;
            }
        } catch (SQLException e) {
            LogUtil.getLog(getClass()).error(e);
        } finally {
            jt.close();
        }

        json.put("list", list);
        json.put("total", totalWorkflow);
        json.put("leafName", leafName);
        return new Result<>(json);
    }

    @ApiOperation(value = "部门人员绩效年统计", notes = "部门人员绩效年统计", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "deptCode", value = "部门编码", required = true, dataType = "String"),
            @ApiImplicitParam(name = "showYear", value = "年份", required = false, dataType = "Integer"),
    })
    @ApiResponses({ @ApiResponse(code = 200, message = "操作成功") })
    @PreAuthorize("hasAnyAuthority('admin', 'admin.flow')")
    @ResponseBody
    @RequestMapping(value = "/flow/stat/performance/statDeptPersonByYear", method = RequestMethod.POST, produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    public Result<Object> statDeptPersonByYear(@RequestParam(value="deptCode") String deptCode, @RequestParam(value="showYear", required = false) Integer showYear) {
        if (StrUtil.isEmpty(deptCode)) {
            Privilege privilege = new Privilege();
            if (!privilege.isUserPrivValid(request, "admin")) {
                return new Result<>(false, i18nUtil.get("pvg_invalid"));
            }
        }

        String leafName = "全部";
        if (!StrUtil.isEmpty(deptCode)) {
            DeptDb leaf = null;
            DeptMgr dir = new DeptMgr();
            leaf = dir.getDeptDb(deptCode);
            leafName = leaf.getName();
        }

        JSONObject json = new JSONObject();
        Calendar cal = Calendar.getInstance();

        if (showYear == null) {
            showYear = cal.get(Calendar.YEAR);
        }

        int totalWorkflow = 0;
        JSONArray list = new JSONArray();
        Calendar showCal = Calendar.getInstance();
        String sql;
        double sum_perf = 0.0;
        double perf = 0.0;
        JdbcTemplate jt = new JdbcTemplate();
        jt.setAutoClose(false);
        try {
            for (int i = 0; i <= 11; i++) {
                showCal.set(showYear, i, 1);

                String bdate = SQLFilter.getDateStr(DateUtil.format(showCal, "yyyy-MM-dd 00:00:00"), "yyyy-MM-dd HH:mm:ss");

                Calendar nextCal = Calendar.getInstance();
                if (i <= 10) {
                    nextCal.set(showYear, i + 1, 1);
                } else {
                    nextCal.set(showYear + 1, 0, 1);
                }
                String edate = SQLFilter.getDateStr(DateUtil.format(nextCal, "yyyy-MM-dd 00:00:00"), "yyyy-MM-dd HH:mm:ss");

                DeptDb deptDb = new DeptDb();
                deptDb = deptDb.getDeptDb(deptCode);
                Vector dv = new Vector();
                deptDb.getAllChild(dv, deptDb);
                String depts = StrUtil.sqlstr(deptCode);
                for (Object value : dv) {
                    deptDb = (DeptDb) value;
                    depts += "," + StrUtil.sqlstr(deptDb.getCode());
                }

                DeptUserDb jd = new DeptUserDb();
                String sql_user = "select du.ID from dept_user du, users u where du.user_name=u.name and u.isValid=1 and du.DEPT_CODE in (" + depts + ")";
                String user_name = "", realName = "";

                sum_perf = 0.0;
                Vector v = jd.listBySQL(sql_user);
                for (Object o : v) {
                    DeptUserDb pu = (DeptUserDb) o;
                    user_name = pu.getUserName();
                    realName = userCache.getUser(user_name).getRealName();

                    sql = "select sum(performance) from flow_my_action where (user_name=" + StrUtil.sqlstr(user_name) + " or proxy=" + StrUtil.sqlstr(user_name) + ") and is_checked=1 and checker=" + StrUtil.sqlstr(user_name) + " and receive_date>=" + bdate + " and receive_date<=" + edate + "";

                    ResultIterator ri = jt.executeQuery(sql);
                    perf = 0.0;
                    if (ri.hasNext()) {
                        ResultRecord rr = ri.next();
                        perf = rr.getDouble(1);
                    }

                    sum_perf += perf;
                }

                JSONObject jsonObject = new JSONObject();
                jsonObject.put("name", i + 1);
                jsonObject.put("value", sum_perf);
                list.add(jsonObject);

                totalWorkflow += sum_perf;
            }
        } catch (SQLException e) {
            LogUtil.getLog(getClass()).error(e);
        } finally {
            jt.close();
        }

        json.put("list", list);
        json.put("total", totalWorkflow);
        json.put("leafName", leafName);
        return new Result<>(json);
    }

    @ApiOperation(value = "月流程处理次数", notes = "月流程处理次数", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "deptCode", value = "部门编码", required = true, dataType = "String"),
            @ApiImplicitParam(name = "op", value = "操作", required = false, dataType = "String"),
            @ApiImplicitParam(name = "page", value = "页码", required = false, dataType = "Integer"),
            @ApiImplicitParam(name = "pageSize", value = "每页条数", required = false, dataType = "Integer"),
    })
    @ApiResponses({ @ApiResponse(code = 200, message = "操作成功") })
    @PreAuthorize("hasAnyAuthority('admin', 'admin.flow')")
    @ResponseBody
    @RequestMapping(value = "/flow/stat/performance/listCountByDept", method = RequestMethod.POST, produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    public Result<Object> listCountByDept(@RequestParam(value="deptCode") String deptCode, @RequestParam(value="op", required = false) String op, @RequestParam(value="page", required = false, defaultValue = "1") Integer page, @RequestParam(value="pageSize", required = false, defaultValue = "20") Integer pageSize) {
        return new Result<>(workflowStatService.listCountByDept(deptCode, op, "", page, pageSize));
    }

    @ApiOperation(value = "月流程处理平均绩效", notes = "月流程处理平均绩效", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "deptCode", value = "部门编码", required = true, dataType = "String"),
            @ApiImplicitParam(name = "op", value = "操作", required = false, dataType = "String"),
            @ApiImplicitParam(name = "page", value = "页码", required = false, dataType = "Integer"),
            @ApiImplicitParam(name = "pageSize", value = "每页条数", required = false, dataType = "Integer"),
    })
    @ApiResponses({ @ApiResponse(code = 200, message = "操作成功") })
    @PreAuthorize("hasAnyAuthority('admin', 'admin.flow')")
    @ResponseBody
    @RequestMapping(value = "/flow/stat/performance/listAverageByDept", method = RequestMethod.POST, produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    public Result<Object> listAverageByDept(@RequestParam(value="deptCode") String deptCode, @RequestParam(value="op", required = false) String op, @RequestParam(value="page", required = false, defaultValue = "1") Integer page, @RequestParam(value="pageSize", required = false, defaultValue = "20") Integer pageSize) {
        return new Result<>(workflowStatService.listAverageByDept(deptCode, op, "", page, pageSize));
    }

    @ApiOperation(value = "导出部门数量", notes = "导出部门数量", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "deptCode", value = "部门编码", required = true, dataType = "String"),
            @ApiImplicitParam(name = "op", value = "操作", required = false, dataType = "String"),
            @ApiImplicitParam(name = "page", value = "页码", required = false, dataType = "Integer"),
            @ApiImplicitParam(name = "pageSize", value = "每页条数", required = false, dataType = "Integer"),
    })
    @RequestMapping("/flow/stat/exportListCountByDept")
    public void exportListCountByDept(HttpServletResponse response, @RequestParam(value="deptCode") String deptCode, @RequestParam(value="op", required = false) String op, @RequestParam(value="page", required = false, defaultValue = "1") Integer page, @RequestParam(value="pageSize", required = false, defaultValue = "20") Integer pageSize) throws IOException {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        String fileName = "流程数量统计.xls";
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);

        OutputStream os = response.getOutputStream();
        try {
            Workbook wb = Workbook.getWorkbook(configUtil.getFile("templ/performance_stat.xls"));
            WritableWorkbook workbook = Workbook.createWorkbook(os, wb);
            WritableSheet sheet = workbook.getSheet(0);
            JSONObject jsonObject = workflowStatService.listCountByDept(deptCode, op, "export", page, pageSize);
            JSONArray list = jsonObject.getJSONArray("list");

            int i = 0;
            for (Object o : list) {
                JSONObject json = (JSONObject)o;
                Label label = new Label(1, i+1, json.getString("realName"));
                sheet.addCell(label);
                label = new Label(2, i+1, json.getString("deptName"));
                sheet.addCell(label);

                for (int k = 0; k<=12; k++) {
                    label = new Label(3+k, i+1, json.getString(String.valueOf(k)));
                    sheet.addCell(label);
                }

                label = new Label(15, i+1, json.getString("amount"));
                sheet.addCell(label);

                i++;
            }
            workbook.write();
            workbook.close();
            wb.close();
        } catch (Exception e) {
            LogUtil.getLog(getClass()).error(e);
        } finally {
            os.close();
        }
    }

    @ApiOperation(value = "导出部门平均值", notes = "导出部门平均值", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "deptCode", value = "部门编码", required = true, dataType = "String"),
            @ApiImplicitParam(name = "op", value = "操作", required = false, dataType = "String"),
            @ApiImplicitParam(name = "page", value = "页码", required = false, dataType = "Integer"),
            @ApiImplicitParam(name = "pageSize", value = "每页条数", required = false, dataType = "Integer"),
    })
    @RequestMapping("/flow/stat/exportListAverageByDept")
    public void exportListAverageByDept(HttpServletResponse response, @RequestParam(value="deptCode") String deptCode, @RequestParam(value="op", required = false) String op, @RequestParam(value="page", required = false, defaultValue = "1") Integer page, @RequestParam(value="pageSize", required = false, defaultValue = "20") Integer pageSize) throws IOException {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        String fileName = "月平均绩效.xls";
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);

        OutputStream os = response.getOutputStream();
        try {
            Workbook wb = Workbook.getWorkbook(configUtil.getFile("templ/performance_stat.xls"));
            WritableWorkbook workbook = Workbook.createWorkbook(os, wb);
            WritableSheet sheet = workbook.getSheet(0);
            JSONObject jsonObject = workflowStatService.listAverageByDept(deptCode, op, "export", page, pageSize);
            JSONArray list = jsonObject.getJSONArray("list");

            int i = 0;
            for (Object o : list) {
                JSONObject json = (JSONObject)o;
                Label label = new Label(1, i+1, json.getString("realName"));
                sheet.addCell(label);
                label = new Label(2, i+1, json.getString("deptName"));
                sheet.addCell(label);

                for (int k = 0; k<=12; k++) {
                    label = new Label(3+k, i+1, json.getString(String.valueOf(k)));
                    sheet.addCell(label);
                }

                label = new Label(15, i+1, json.getString("amount"));
                sheet.addCell(label);

                i++;
            }
            workbook.write();
            workbook.close();
            wb.close();
        } catch (Exception e) {
            LogUtil.getLog(getClass()).error(e);
        } finally {
            os.close();
        }
    }

    @ApiOperation(value = "列出用户参与的流程绩效", notes = "列出用户参与的流程绩效", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "op", value = "操作", required = false, dataType = "String"),
    })
    @ApiResponses({ @ApiResponse(code = 200, message = "操作成功") })
    @PreAuthorize("hasAnyAuthority('admin', 'admin.flow')")
    @ResponseBody
    @RequestMapping(value = "/flow/stat/performance/listUserAttend", method = RequestMethod.POST, produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    public Result<Object> listUserAttend(@RequestParam(value="op", required = false) String op) {
        Privilege privilege = new Privilege();
        String myname = ParamUtil.get(request, "userName");
        if ("".equals(myname)) {
            myname = privilege.getUser(request);
        }
        if (!myname.equals(privilege.getUser(request))) {
            if (!privilege.canAdminUser(request, myname)) {
                return new Result<>(false, i18nUtil.get("pvg_invalid"));
            }
        }

        String typeCode = ParamUtil.get(request, "typeCode");
        String title = ParamUtil.get(request, "title");
        String idcard = ParamUtil.get(request, "idcard");
        String fqr = ParamUtil.get(request,"fqr");
        String isOver = ParamUtil.get(request,"isOver");

        com.alibaba.fastjson.JSONObject json = new com.alibaba.fastjson.JSONObject();
        int pagesize = ParamUtil.getInt(request, "pageSize", 20);
        int curpage = ParamUtil.getInt(request, "page", 1);

        WorkflowDb wf = new WorkflowDb();
        MyActionDb mad = new MyActionDb();

        String sql = "select id from flow_my_action where (user_name=" + StrUtil.sqlstr(myname) + " or proxy=" + StrUtil.sqlstr(myname) + ")";
        if ("search".equals(op)) {
            sql = "select m.id from flow_my_action m, flow f where m.flow_id=f.id and (m.user_name=" + StrUtil.sqlstr(myname) + " or m.proxy=" + StrUtil.sqlstr(myname) + ")";
            if(!"".equals(fqr)){
                sql = "select m.id from flow_my_action m, flow f, user u where u.name=f.userName and m.flow_id=f.id and (m.user_name=" + StrUtil.sqlstr(myname) + " or m.proxy=" + StrUtil.sqlstr(myname) + ")";
                sql += "  and u.realName like " + StrUtil.sqlstr("%" + fqr + "%");
            }
            if (!"".equals(typeCode)) {
                sql += " and f.type_code=" + StrUtil.sqlstr(typeCode);
            }
            if (!"".equals(title)) {
                sql += " and f.title like " + StrUtil.sqlstr("%" + title + "%");
            }
            if (!"".equals(idcard)) {
                sql += " and f.id = " +StrUtil.sqlstr(idcard) ;
            }

            if(!"".equals(isOver)){
                sql += "  and f.status ="+StrUtil.sqlstr(isOver);
            }
        }
        sql += " order by id desc";

        // out.print(sql);

        ListResult lr = mad.listResult(sql, curpage, pagesize);

        long total = lr.getTotal();

        Vector v = lr.getResult();
        Iterator ir = v.iterator();

        JSONArray list = new JSONArray();
        Leaf ft = new Leaf();
        WorkflowActionDb wad = new WorkflowActionDb();
        com.redmoon.oa.person.UserMgr um = new com.redmoon.oa.person.UserMgr();
        while (ir.hasNext()) {
            mad = (MyActionDb) ir.next();
            WorkflowDb wfd = new WorkflowDb();
            wfd = wfd.getWorkflowDb((int) mad.getFlowId());
            UserDb user = null;
            if (wfd.getUserName() != null) {
                user = um.getUserDb(wfd.getUserName());
            }
            String realName = "";
            if (user != null) {
                realName = user.getRealName();
            }

            JSONObject row = new JSONObject();
            row.put("flowId", wfd.getId());
            row.put("realName", realName);
            row.put("title", wfd.getTitle());

            wad = wad.getWorkflowActionDb((int)mad.getActionId());
	        row.put("actionTitle", wad.getTitle());

	        String checker = mad.getChecker();
	        row.put("checker", um.getUserDb(checker).getRealName());

	        row.put("level", wfd.getLevel());
            row.put("reason", mad.getPerformanceReason());
            row.put("modifier", mad.getPerformanceModifier());

            if (mad.getChecker().equals(UserDb.SYSTEM)) {
                row.put("checkStatusName", "跳过");
            }else{
                row.put("checkStatusName", mad.getCheckStatusName());
            }

            row.put("checkDate", DateUtil.format(mad.getCheckDate(), "yy-MM-dd HH:mm"));

            Leaf lf = ft.getLeaf(wfd.getTypeCode());
            row.put("typeName", lf.getName());
            row.put("typeCode", wfd.getTypeCode());

            row.put("performance", NumberUtil.round(mad.getPerformance(), 2));
            row.put("myActionId", mad.getId());

            list.add(row);
        }

        json.put("list", list);
        json.put("total", total);
        return new Result<>(json);
    }

    @ApiOperation(value = "修改绩效", notes = "修改绩效", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "myActionId", value = "办理记录ID", required = true, dataType = "Long"),
            @ApiImplicitParam(name = "performance", value = "绩效", required = true, dataType = "Double"),
            @ApiImplicitParam(name = "reason", value = "操作", required = true, dataType = "String"),
    })
    @ApiResponses({ @ApiResponse(code = 200, message = "操作成功") })
    @PreAuthorize("hasAnyAuthority('admin', 'admin.flow')")
    @ResponseBody
    @RequestMapping(value = "/flow/stat/performance/updateByMyActionId", method = RequestMethod.POST, produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    public Result<Object> updateByMyActionId(@RequestParam(value="myActionId") Long myActionId, @RequestParam(value="performance") Double performance, @RequestParam(value="reason") String reason) {
        MyActionDb mad = new MyActionDb();
        mad = mad.getMyActionDb(myActionId);
        /*WorkflowDb wf = new WorkflowDb();
        wf = wf.getWorkflowDb((int)mad.getFlowId());

        LeafPriv leafPriv = new LeafPriv(wf.getTypeCode());
        if (!leafPriv.canUserExamine(authUtil.getUserName())) {
            return new Result<>(false, i18nUtil.get("pvg_invalid"));
        }*/

        Privilege privilege = new Privilege();
        if (!privilege.canAdminUser(request, mad.getUserName())) {
            return new Result<>(false, i18nUtil.get("pvg_invalid"));
        }

        mad.setPerformance(performance);
        mad.setPerformanceReason(reason);
        mad.setPerformanceModify(authUtil.getUserName());
        return new Result<>(mad.save());
    }

    @ApiOperation(value = "流程统计", notes = "流程统计", httpMethod = "POST")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "showYear", value = "年份", required = false, dataType = "Integer"),
            @ApiImplicitParam(name = "page", value = "页码", required = false, dataType = "Integer"),
            @ApiImplicitParam(name = "pageSize", value = "每页条数", required = false, dataType = "Integer"),
    })
    @ApiResponses({ @ApiResponse(code = 200, message = "操作成功") })
    @PreAuthorize("hasAnyAuthority('admin', 'admin.flow')")
    @ResponseBody
    @RequestMapping(value = "/flow/stat/list", method = RequestMethod.POST, produces = {"text/html;charset=UTF-8;", "application/json;charset=UTF-8;"})
    public Result<Object> list(@RequestParam(value="showYear", required = false) Integer showYear, @RequestParam(value="page", required = false, defaultValue = "1") Integer page, @RequestParam(value="pageSize", required = false, defaultValue = "20") Integer pageSize) {
        Privilege privilege = new Privilege();
        String curUnitCode = ParamUtil.get(request, "unitCode");
        if ("".equals(curUnitCode)) {
            curUnitCode = privilege.getUserUnitCode(request);
        }
        // 检查用户能否管理该单位
        if (!privilege.canUserAdminUnit(request, curUnitCode)) {
            return new Result<>(false, i18nUtil.get("pvg_invalid"));
        }

        String flowTypeCode = ParamUtil.get(request, "flowTypeCode");
        String flowTypeName = "";
        if (!"".equals(flowTypeCode)) {
            Leaf flf = new Leaf();
            flf = flf.getLeaf(flowTypeCode);
            flowTypeName = flf.getName();

            LeafPriv lp = new LeafPriv(flowTypeCode);
            if (!(lp.canUserExamine(privilege.getUser(request)))) {
                return new Result<>(false, "请选择流程");
            }
        } else {
            if (!privilege.isUserPrivValid(request, "admin.flow")) {
                return new Result<>(false, i18nUtil.get("pvg_invalid"));
            }
        }

        Calendar cal = Calendar.getInstance();
        if (showYear == null) {
            showYear = cal.get(Calendar.YEAR);
        }

        FormDb ftd = new FormDb();
        String sql = "";
        if (!"".equals(flowTypeCode)) {
            sql = "select code from " + ftd.getTableName() + " where flowTypeCode=" + StrUtil.sqlstr(flowTypeCode) + " and isFlow=1 order by code asc";
        } else {
            sql = "select code from " + ftd.getTableName() + " where isFlow=1 and unit_code=" + StrUtil.sqlstr(curUnitCode) + " order by code asc";
        }

        long total = 0;
        JSONArray ary = new JSONArray();
        JdbcTemplate jt = new JdbcTemplate();
        jt.setAutoClose(false);
        try {
            ListResult lr = ftd.listResult(sql, page, pageSize);
            total = lr.getTotal();
            for (Object o : lr.getResult()) {
                ftd = (FormDb) o;
                if (ftd == null || !ftd.isLoaded()) {
                    continue;
                }
                try {
                    sql = "select count(f.id) from flow f," + ftd.getTableNameByForm() + " m where f.id = m.flowId and " + SQLFilter.year("f.mydate") + " = " + StrUtil.sqlstr(String.valueOf(showYear)) + "  and f.status=" + WorkflowDb.STATUS_NOT_STARTED;

                    ResultIterator ri = jt.executeQuery(sql);
                    int countNot = 0;
                    if (ri.hasNext()) {
                        ResultRecord rr = ri.next();
                        countNot = rr.getInt(1);
                    }

                    sql = "select count(f.id) from flow f," + ftd.getTableNameByForm() + " m where f.id = m.flowId and " + SQLFilter.year("f.mydate") + " = " + StrUtil.sqlstr(String.valueOf(showYear)) + "  and f.status=" + WorkflowDb.STATUS_STARTED;
                    ri = jt.executeQuery(sql);
                    int countStarted = 0;
                    if (ri.hasNext()) {
                        ResultRecord rr = ri.next();
                        countStarted = rr.getInt(1);
                    }
                    sql = "select count(f.id) from flow f," + ftd.getTableNameByForm() + " m where f.id = m.flowId and " + SQLFilter.year("f.mydate") + " = " + StrUtil.sqlstr(String.valueOf(showYear)) + "  and f.status=" + WorkflowDb.STATUS_FINISHED;
                    ri = jt.executeQuery(sql);
                    int countFinished = 0;
                    if (ri.hasNext()) {
                        ResultRecord rr = ri.next();
                        countFinished = rr.getInt(1);
                    }
                    sql = "select count(f.id) from flow f," + ftd.getTableNameByForm() + " m where f.id = m.flowId and " + SQLFilter.year("f.mydate") + " = " + StrUtil.sqlstr(String.valueOf(showYear)) + "  and f.status=" + WorkflowDb.STATUS_DISCARDED;
                    ri = jt.executeQuery(sql);
                    int countDiscarded = 0;
                    if (ri.hasNext()) {
                        ResultRecord rr = ri.next();
                        countDiscarded = rr.getInt(1);
                    }
                    int flowSum = countNot + countStarted + countFinished + countDiscarded;
                    int averageMonth = flowSum / 12;
                    int averageDay = flowSum / 365;

                    JSONObject json = new JSONObject();
                    json.put("name", ftd.getName());
                    json.put("countNot", countNot);
                    json.put("countStarted", countStarted);
                    json.put("countFinished", countFinished);
                    json.put("countDiscarded", countDiscarded);
                    json.put("flowSum", flowSum);
                    json.put("averageMonth", averageMonth);
                    json.put("averageDay", averageDay);

                    String code = ftd.getCode();
                    Leaf leaf = new Leaf();
                    leaf = leaf.getLeafByFormCode(code);
                    boolean isFlowExist = leaf != null;
                    json.put("isFlowExist", isFlowExist);
                    if (isFlowExist) {
                        json.put("typeCode", leaf.getCode());
                        json.put("typeName", leaf.getName());
                        json.put("layer", leaf.getLayer());
                    }

                    ary.add(json);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } finally {
            jt.close();
        }
        JSONObject json = new JSONObject();
        json.put("total", total);
        json.put("list", ary);
        return new Result<>(json);
    }
}
